Query Excel file source through Linked Server
In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.
When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.
You just need to create the Excel file and execute the following SQL Statements below:
–> For Excel 2003 format:
USE MSDB GO EXEC sp_addLinkedServer @server= 'XLS_NewSheet', @srvproduct = 'Jet 4.0', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:\Manoj_Advantage\NewSheet.xls', @provstr = 'Excel 5.0; HDR=Yes'
– Now, query your excel file in two ways:
SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]') SELECT * FROM XLS_NewSheet...[Sheet1$]
–> For Excel 2007 format:
USE MSDB GO EXEC sp_addLinkedServer @server= 'XLSX_NewSheet', @srvproduct = 'ACE 12.0', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx', @provstr = 'Excel 12.0; HDR=Yes'
– Now, query your excel file in two ways:
SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]') SELECT * FROM XLSX_NewSheet...[Sheet1$]
Note: If your excel file don’t have headers, then set HDR=No
You may need to execute the following SQL Statements to configure the Linked Server initially:
USE MSDB GO sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OverRide GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE WITH OverRide GO
>> Check & Subscribe my [YouTube videos] on SQL Server.
Connect CSV: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24225938.html
EXEC sp_addlinkedserver MyCSV, ‘Jet 4.0’, ‘Microsoft.Jet.OLEDB.4.0′,’c:\’, NULL,’Text’
GO
EXEC sp_addlinkedsrvlogin MyCSV, FALSE, NULL,NULL,NULL
GO
EXEC sp_tables_ex MyCSV — note how it picks up any / all txt and csv files in that folder
GO
select * from MyCSV…Order_Worksheet#csv — four part identifier using table name from above
GO
select * from openquery(MyCSV,’select * from order_worksheet.csv’)
GO
sp_dropserver ‘MyCSV’, ‘droplogins’;
GO
Issues with 32 & 64 bit server MSDN post:-
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f1015781-92cd-4783-9b5d-4fb27880d901
Resolution:-
https://connect.microsoft.com/SQLServer/feedback/details/581640/trying-to-use-openrowset-to-import-2003-excel-file-into-64-bit-2008-r2-sql-server?wa=wsignin1.0
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
Hi manub22
Thanks for the great article … but when I tried your steps to add in a xlsx file I got the following error – “The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked serverreported an error. Access denied.”
Fir this I went to services.msc and went to the properties of SQLEXPRESS .. I made sure that my system user name and password were entered to the this account section in the Log on tab
And then I restarted the service and my error was gone !
–Saurabh
Thanks for the article,i want to link sql server to an Excel file in another Pc in the same network?could you help please
@bnar, you can provide the network path at the @datasrc parameter. You will need to share the folder where Excel file is stored on another PC.
Hi I tried above but getting error.
Msg 7438, Level 16, State 1, Line 41
The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0” cannot be loaded in-process on a 64-bit SQL Server.
Can this work with SQL Authentication or only Windows Auth?
This will work with both SQL & Win Auth.